package abc.database.interfaces;

import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public interface PostgresqlHelper extends ISqlHelper<Map<String, Object>> {
    default List<Map<String, Object>> getTableList(String dbname, JdbcTemplate jdbcTemplate) {
        String sql = "select relname as table_name, '' as table_comment, 'table' as type from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname";
        return jdbcTemplate.queryForList(sql);
    }

    default List<Map<String, Object>> getColumnList(String dbname, JdbcTemplate jdbcTemplate) {
        List<Map<String, Object>> columns = new ArrayList<>();
        List<Map<String, Object>> tableList = this.getTableList(dbname, jdbcTemplate);
        if(tableList == null) {
            return columns;
        }
        tableList.forEach(a-> {
            if(!a.containsKey("table_name") || a.get("table_name")==null) {
                return;
            }
            String tablename = a.get("table_name").toString();
            String sql = String.format( "SELECT '%s' as table, col_description(a.attrelid,a.attnum) as comment,format_type(a.atttypid,a.atttypmod) as dataType,a.attname as annotationColumnName,a.attname as propertyName,a.attname as name, a.attnotnull as notnull, 0 as isAutoIncrement, 1 as convert, 0 as versionField, 0 as keyFlag, 0 as keyIdentityFlag FROM pg_class as c inner join pg_attribute as a on c.relname='%s' and a.attrelid = c.oid and a.attnum>0;", tablename, tablename);
            List<Map<String, Object>> cols = jdbcTemplate.queryForList(sql);
            if(cols!=null && cols.size()>0) {
                columns.addAll(cols);
            }
        });

        return columns;
    }
}
